USE master;
GO

-- allow contained database authentication for the instance
/*
EXECUTE sp_configure 'contained database authentication', 1;
RECONFIGURE;
GO
*/

-- create a contained database
CREATE DATABASE HungryDatabase
CONTAINMENT = PARTIAL
ON PRIMARY
	(NAME = N'HungryDatabase',
	FILENAME = N'C:\MSSQL\Data\HungryDatabase.mdf',
	SIZE = 128MB, FILEGROWTH = 64MB)
LOG ON
	(NAME = N'HungryDatabase_Log',
	FILENAME = N'C:\MSSQL\Data\HungryDatabase.ldf',
	SIZE = 64MB, FILEGROWTH = 32MB);

ALTER DATABASE HungryDatabase SET RECOVERY SIMPLE;
GO

USE HungryDatabase;
GO

-- create a contained user with password
CREATE USER Bob WITH PASSWORD = N'pass@w0rd', DEFAULT_SCHEMA = [dbo];

-- make Bob the db_owner
EXECUTE sp_addrolemember 'db_owner', 'Bob';
GO

-- create a big table table
CREATE TABLE dbo.BigTable (
	SalesID BIGINT IDENTITY NOT NULL,
	SalesDate DATETIME NOT NULL,
	SalesDescription VARCHAR (100) NOT NULL,
	CustomerID INT NOT NULL,
	ProductID INT NOT NULL,
	PayDate DATETIME DEFAULT NULL,
	Quantity INT NOT NULL,
	Price DECIMAL (6,2) NOT NULL,
	Discount INT DEFAULT (0) NOT NULL,
	WarehouseID INT NOT NULL,
	PickerID INT NOT NULL,
	ShipperID INT NOT NULL);
GO

-- create a resource pool and a workload group
USE master;
GO

CREATE RESOURCE POOL HungryUserPool
WITH (MAX_CPU_PERCENT = 60,
	MAX_IOPS_PER_VOLUME = 100);

CREATE WORKLOAD GROUP HungryUserGroup
USING HungryUserPool;
GO

/*
-- SQL Sever 2008
MAX_CPU_PERCENT: Maximum average CPU bandwith that all requests
in the resource pool will receive when there is CPU contention.

-- SQL Server 2012:
CAP_CPU_PERCENT: Hard cap on the CPU bandwith that all requests
in the resource pool receive.

-- SQL Server 2014:
MAX_IOPS_PER_VOLUME: Maximum I/PO operations per second
per disk volumne for workloads and groups of workloads.
*/

-- add the resource governor classifier function
CREATE FUNCTION dbo.ResourceGovernorClassifier()
RETURNS SYSNAME WITH SCHEMABINDING
AS
BEGIN
 DECLARE @GroupName SYSNAME;

 IF IS_SRVROLEMEMBER ('sysadmin') = 1 OR IS_SRVROLEMEMBER('serveradmin') = 1 
	OR IS_SRVROLEMEMBER('securityadmin') = 1 OR IS_SRVROLEMEMBER('processadmin') = 1
	OR IS_SRVROLEMEMBER('diskadmin') = 1
	BEGIN
		SELECT @GroupName = 'AdminGroup';
	END
 ELSE
	IF ORIGINAL_DB_NAME() = 'HungryDatabase'  -- SUSER_NAME, APP_NAME, ORIGINAL_DB_NAME
		-- SELECT @db = CONVERT(nvarchar, LOGINPROPERTY(SUSER_NAME(), 'DefaultDatabase'));
		SET @GroupName = 'HungryUserGroup';
	ELSE
		SET @GroupName = 'Default';
	
	RETURN @GroupName;
END
GO

-- assign the classifier function
ALTER RESOURCE GOVERNOR
WITH (CLASSIFIER_FUNCTION = dbo.ResourceGovernorClassifier);

-- make it hot
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

/*
Open Performance Monitor and add the following counters
for the "HungryUserPool" instance:
"SQL Server:Resource Pool Stats:Disk Write IO/Sec"
"SQL Server:Resource Pool Stats:Disk Write IO Throttled/Sec"
*/

-- run the IO workload query, connected as Bob to teh HungryDatabase

-- reconfigure the resource pool
ALTER RESOURCE POOL HungryUserPool
WITH (CAP_CPU_PERCENT = 40,
	MAX_IOPS_PER_VOLUME = 20);
GO

-- make it hot
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

-- note: yould see an increase of the PAGEIOLATCH_EX and SOS_SCHEDULER_YIELD wait types
-- when IO trottling your workload

-- cleanup
USE master;
GO

ALTER RESOURCE GOVERNOR
WITH (CLASSIFIER_FUNCTION = NULL);

ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

DROP FUNCTION dbo.ResourceGovernorClassifier;
GO
DROP WORKLOAD GROUP HungryUserGroup;
GO
DROP RESOURCE POOL HungryUserPool;
GO

IF DATABASEPROPERTYEX('HungryDatabase', 'Version') > 0
BEGIN
	ALTER DATABASE HungryDatabase
		SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
	DROP DATABASE HungryDatabase;
END